package data;

import com.sun.org.apache.regexp.internal.RE;
import data.dataStruct.DataStruct;
import data.dataStruct.Review;
import servlet.Control;

import java.sql.*;
import java.util.*;

//查询控制类
public class Select {
    /**
     * 翻译员详细资料查询
     * @param id 翻译员id
     * @return 字段哈希表
     */
    public static Map<String ,Object> profileDetail(int id){
        String sql="SELECT * FROM translator WHERE id=?;";

        Map<String,Object> map=new HashMap<>();
        Connection connection=DatabaseManage.getConnection();

        try {
            PreparedStatement st=connection.prepareStatement(sql);
            st.setInt(1,id);
            ResultSet rs=st.executeQuery();

            //因为是主键查找，所以最多只能出现一个客户被查找到
            rs.next();
            ResultSetMetaData data=rs.getMetaData();
            for(int i=1;i<=data.getColumnCount();i++){
                String name=data.getColumnName(i);
                String value=rs.getString(i);
                map.put(name,value);
            }

            rs.close();
            st.close();
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return map;
    }

    //查询最顶层的6个翻译员头像
    public static List<DataStruct> topImg(){
        String sql="SELECT id,image FROM translator WHERE visit=1 ORDER BY priority DESC LIMIT 6;";
        return selectDataStruct(sql);
    }


    /**
     * 查询某个翻译员的所有review
     * @param id 翻译员id
     * @return review表
     */
    public static List<Review> review (String id){
        String sql="SELECT id,tid,title,content FROM review WHERE tid=?;";
        String[] strings={id};
        return selectReview(sql,strings);
    }
    //查询最顶层的6个翻译员的review
    public static List<Review> topReview(){
        String sql="SELECT review.id,tid,title,content\n" +
                "FROM review INNER JOIN translator ON review.tid = translator.id\n" +
                "WHERE visit=1 ORDER BY priority DESC\n" +
                "LIMIT 6;";

        String[] strings={};
        return selectReview(sql,strings);
    }
    private static List<Review> selectReview(String sql,String[] strings){
        Connection connection=DatabaseManage.getConnection();
        List<Review> list=new ArrayList<>();

        try {
            PreparedStatement st=connection.prepareStatement(sql);
            for(int i=0;i<strings.length;i++){
                st.setString(i+1,strings[i]);
            }
            ResultSet rs=st.executeQuery();

            while (rs.next()){
                Review review=new Review(rs.getString(1), rs.getString(2),
                        rs.getString(3),rs.getString(4));

                list.add(review);
            }

            rs.close();
            st.close();
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return list;
    }

    //查询某个翻译员的所有展示图片
    public static List<DataStruct> gallery(int id){
        String sql="SELECT id,img FROM gallery WHERE tid="+id+";";
        return selectDataStruct(sql);
    }

    //查询某个翻译员的照片位置
    public static String profilePicture(int id){
        String sql="SELECT image FROM translator WHERE id=?;";

        Connection connection=DatabaseManage.getConnection();

        try {
            PreparedStatement st=connection.prepareStatement(sql);
            st.setInt(1,id);
            ResultSet rs=st.executeQuery();

            rs.next();
            String path=rs.getString(1);

            rs.close();
            st.close();
            return path;
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return "";
    }
    //查询某个翻译员的城市
    public static List<DataStruct> profileCity (int id){
        String sql="SELECT cid,name\n" +
                "FROM city INNER JOIN translator_city ON city.id = translator_city.cid\n" +
                "WHERE tid="+id+";";

        return selectDataStruct(sql);
    }
    //查询某个翻译员的语言
    public static List<DataStruct> profileLanguage (int id){
        String sql="SELECT lid,name\n" +
                "FROM language INNER JOIN translator_language ON language.id = translator_language.lid\n" +
                "WHERE tid="+id+";";

        return selectDataStruct(sql);
    }
    //查询某个DataStruct
    private static List<DataStruct> selectDataStruct (String sql){
        List<DataStruct> list=new ArrayList<>();
        Connection connection=DatabaseManage.getConnection();

        try {
            PreparedStatement st=connection.prepareStatement(sql);
            ResultSet rs=st.executeQuery();

            while (rs.next()){
                list.add(new DataStruct(rs.getString(1),rs.getString(2)));
            }

            rs.close();
            st.close();
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return list;
    }

    //查询所有的城市
    public static List<DataStruct> allCity(){
        String sql="SELECT * FROM city;";
        return selectDataStruct(sql);
    }

    //查询所有的语言
    public static List<DataStruct> allLanguage(){
        String sql="SELECT * FROM language;";
        return selectDataStruct(sql);
    }

    //浏览客户根据匹配查询profile
    public static List<HashMap<String,String>> profilesEllipsis(List<String> tid,int page){
        StringBuffer sql=new StringBuffer("SELECT id,image,educational,translation_experience,language_proficiency,introduction " +
                "FROM translator WHERE 1=2");

        for(int i=0;i<tid.size();i++){
            sql.append(" OR id="+tid.get(i));
        }

        sql.append(" LIMIT "+((page-1))*8+",8;");

        return selectProfiles(sql.toString());
    }
    //管理员端查询profile卡片
    public static List<HashMap<String,String>> profilesCard(int page){
        String sql="SELECT id,image,name,visit,pwd\n" +
                "FROM translator\n" +
                "LIMIT "+((page-1)*8)+",8;";

        return selectProfiles(sql);
    }
    //分页查询,返回一个List
    private static List<HashMap<String,String>> selectProfiles(String sql){
        List<HashMap<String ,String >> list=new ArrayList<>();
        Connection connection=DatabaseManage.getConnection();

        try {
            PreparedStatement st=connection.prepareStatement(sql);
            ResultSet rs=st.executeQuery();

            for(int i=0;rs.next();i++){
                ResultSetMetaData data=rs.getMetaData();
                list.add(new HashMap<>());
                for(int j=1;j<=data.getColumnCount();j++){
                    String name=data.getColumnName(j);
                    String value=rs.getString(j);
                    list.get(i).put(name,value);
                }
            }
            rs.close();
            st.close();
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return list;
    }

    //查询一个profile的reviews的个数
    public static int reviewNumber(String id){
        String sql="SELECT COUNT(*) FROM review WHERE tid=?;";
        String[] strings={id};
        return selectCount(sql,strings);
    }
    //查询某个翻译员有多少张照片
    public static int imgNumber(int id){
        String sql="SELECT COUNT(*) FROM gallery WHERE tid=?;";
        String[] strings={id+""};
        return selectCount(sql,strings);
    }
    //查询管理员端profile卡片的数量
    public static int profilesCount(){
        String sql="SELECT COUNT(*) FROM translator;";
        String[] strings={};

        return selectCount(sql,strings);
    }
    //查询某种限制条件下记录条数
    private static int selectCount(String sql,String[] strings){
        Connection connection=DatabaseManage.getConnection();
        try {
            PreparedStatement st=connection.prepareStatement(sql);
            for(int i=0;i<strings.length;i++){
                st.setString(i+1,strings[i]);
            }
            ResultSet rs=st.executeQuery();

            rs.next();
            int count=rs.getInt(1);
            rs.close();
            st.close();
            return count;
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return -1;
    }

    //查询管理员密码
    public static String rootPwd(String pwd){
        String sql="SELECT COUNT(*) FROM root WHERE pwd=MD5(?);";
        List<String> strings=new ArrayList<>();
        strings.add(pwd);

        List<String> list=selectStrings(sql,strings);
        return list.get(0);
    }
    //根据搜索内容查找相应的tid;
    public static List<String> tidBySelect(List<String> lid,List<String> cid){
        StringBuffer sqlLid=new StringBuffer("SELECT tid FROM translator_language WHERE 1=1");
        List<String> strings=new ArrayList<>();

        for(int i=0;i<lid.size();i++){
            sqlLid.append(" AND lid=?");
            strings.add(lid.get(i));
        }

        StringBuffer sqlCid=new StringBuffer("SELECT tid FROM translator_city WHERE 1=1");
        for(int i=0;i<cid.size();i++){
            sqlCid.append(" AND cid=?");
            strings.add(cid.get(i));
        }

        String sql="SELECT DISTINCT c.tid\n" +
                "FROM ("+sqlLid+") AS l ,\n" +
                "  ("+sqlCid+") AS c\n" +
                "WHERE c.tid=l.tid;";
        return selectStrings(sql,strings);
    }
    //执行批处理并返回List<String>
    private static List<String> selectStrings(String sql,List<String> strings){
        List<String> list=new ArrayList<>();
        Connection connection=DatabaseManage.getConnection();

        try {
            PreparedStatement st=connection.prepareStatement(sql);
            for(int i=0;i<strings.size();i++){
                st.setString(i+1,strings.get(i));
            }
            ResultSet rs=st.executeQuery();

            while (rs.next()){
                list.add(rs.getString(1));
            }

            rs.close();
            st.close();
        }catch (SQLException ex){
            ex.printStackTrace();
        }

        return list;
    }
}
